/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import POJO.CTPXuatKho;
import POJO.SanPhamBanTrongNgay;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Hồng
 */
public class CTPXuatKhoDao {

	public static ArrayList<CTPXuatKho> layDanhSach() {
		ArrayList<CTPXuatKho> ds = new ArrayList<CTPXuatKho>();
		String sql = "SELECT  MaCT, SoLuong, MaSP, MaPXK   FROM  CTPXuatKho ";

		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("MaCT");
				int soluong = rs.getInt("SoLuong");
				int masp = rs.getInt("MaSp");
				int mapx = rs.getInt("MaPXK");
				CTPXuatKho tmp = new CTPXuatKho(ma, soluong, masp, mapx);
				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(CTPXuatKhoDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static ArrayList<CTPXuatKho> layDanhSachByMaPX(int MaPXK) {
		ArrayList<CTPXuatKho> ds = new ArrayList<CTPXuatKho>();
		String sql = "SELECT  MaCT, SoLuong, MaSP, MaPXK   FROM  CTPXuatKho  where CTPXuatKho.MaPXK = " + MaPXK;

		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("MaCT");
				int soluong = rs.getInt("SoLuong");
				int masp = rs.getInt("MaSp");
				int mapx = rs.getInt("MaPXK");
				CTPXuatKho tmp = new CTPXuatKho(ma, soluong, masp, mapx);
				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(CTPXuatKhoDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static ArrayList<CTPXuatKho> timKiemByTen(String maPXK) {
		ArrayList<CTPXuatKho> ds = new ArrayList<CTPXuatKho>();

		String sql = "SELECT * FROM CTPXuatKho WHERE MaPXK LIKE '%" + maPXK + "%'";
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("MaCT");
				int soluong = rs.getInt("SoLuong");
				int masp = rs.getInt("MaSp");
				int mapx = rs.getInt("MaPXK");
				CTPXuatKho tmp = new CTPXuatKho(ma, soluong, masp, mapx);
				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(CTPXuatKhoDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static int layMaCTPXKMax() {
		String sql = "select ct.MaCT from CTPXuatKho ct where ct.MaCT >= all(select ct1.MaCT from CTPXuatKho ct1 ); ";
		KetNoi cn = null;
		ResultSet rs = null;
		int n = 0;

		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				n = rs.getInt("MaCT");
			}
		} catch (Exception ex) {
			Logger.getLogger(CTPXuatKhoDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return n;
	}

	public static boolean them(ArrayList<CTPXuatKho> tmp) {
		boolean kq = false;
		int maxct = CTPXuatKhoDao.layMaCTPXKMax();
		maxct += 1;
		String sql = "";
		String sqlUpdate = "";
		for (int i = 0; i < tmp.size(); i++) {
			tmp.get(i).setMaCT(i + maxct);
			String s = String.format("INSERT INTO  CTPXuatKho "
				 + " ( MaCT, SoLuong, MaSP, MaPXK ) "
				 + "VALUES ( %d, %d, %d, %d);",
				 tmp.get(i).getMaCT(),
				 tmp.get(i).getSoLuong(),
				 tmp.get(i).getMaSP(),
				 tmp.get(i).getMaPXK());
			sql = sql + s;

			String sup = String.format("UPDATE SanPham SET "
				 + "SLTon = SLTon -%d "
				 + "WHERE  MaSP = %d;", tmp.get(i).getSoLuong(), tmp.get(i).getMaSP());
			sqlUpdate = sqlUpdate + sup;
		}
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
			int nu = cn.thucThi(sqlUpdate);
		} catch (SQLException ex) {
			Logger.getLogger(CTPXuatKhoDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		if (n >= 1) {
			kq = true;
		}

		return kq;
	}

	public static boolean them(CTPXuatKho tmp) {
		boolean kq = false;
		String sql = "INSERT INTO  CTPXuatKho  ( MaCT, SoLuong, MaSP, MaPXK ) VALUES ";

		String s = String.format("( %d, %d, %d, %d)", tmp.getMaCT(), tmp.getSoLuong(), tmp.getMaSP(), tmp.getMaPXK());
		s += sql;
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
		} catch (SQLException ex) {
			Logger.getLogger(CTPXuatKhoDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		if (n == 1) {
			kq = true;
		}

		return kq;
	}

	public static boolean xoa(int MaCT) {
		boolean kq = false;
		String sql = String.format("DELETE FROM CTPXuatKho WHERE MaCT = %d", MaCT);
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
		} catch (SQLException ex) {
			Logger.getLogger(CTPXuatKhoDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		if (n == 1) {
			kq = true;
		}

		return kq;
	}

	public static boolean capNhat(CTPXuatKho tmp) {
		boolean kq = false;
		String sql = String.format("UPDATE CTPXuatKho"
			 + "SET"
			 + "SoLuong = %d, "
			 + "MaSP = %d,"
			 + "MaPXK = %d"
			 + "WHERE  MaCT = %d; ", tmp.getSoLuong(), tmp.getMaSP(), tmp.getMaPXK(), tmp.getMaCT());
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
		} catch (SQLException ex) {
			Logger.getLogger(CTPXuatKhoDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}

		if (n == 1) {
			kq = true;
		}

		return kq;
	}

	public static CTPXuatKho layByMaCT(int maCT) {
		CTPXuatKho tmp = null;
		KetNoi cn = null;

		try {
			cn = new KetNoi();
			String sql = "SELECT * FROM CTPXuatKho WHERE MaCT =" + maCT;
			ResultSet rs = cn.docBang(sql);
			if (rs.next()) {
				int ma = rs.getInt("MaCT");
				int soluong = rs.getInt("SoLuong");
				int masp = rs.getInt("MaSp");
				int mapx = rs.getInt("MaPXK");
				tmp = new CTPXuatKho(ma, soluong, masp, mapx);
			}
			cn.dongKetNoi();
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		} finally {
			cn.dongKetNoi();
		}
		return tmp;
	}

	public static CTPXuatKho layByMaCTHD(int maCTHD) {
		CTPXuatKho tmp = null;
		KetNoi cn = null;
		try {
			String sql = "SELECT * FROM CTPXuatKho WHERE MaCTHD =" + maCTHD;
			cn = new KetNoi();
			ResultSet rs = cn.docBang(sql);
			if (rs.next()) {
				int ma = rs.getInt("MaCT");
				int soluong = rs.getInt("SoLuong");
				int masp = rs.getInt("MaSp");
				int mapx = rs.getInt("MaPXK");
				tmp = new CTPXuatKho(ma, soluong, masp, mapx);
			}

		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		} finally {
			cn.dongKetNoi();
		}
		return tmp;
	}

	public static ArrayList<SanPhamBanTrongNgay> layDanhSachSanPhamDaXuat(int manv) {
		ArrayList<SanPhamBanTrongNgay> ds = new ArrayList<SanPhamBanTrongNgay>();
		String str = "SELECT sp.TenSP, ctpxk.SoLuong FROM SANPHAM sp, PXUATKHO pxk, CTPXUATKHO ctpxk ";
		str += " where pxk.MaNVPhuTrach = '" + manv + "' and pxk.MaPXK=ctpxk.MaPXK ";
		str += " and sp.MaSP=ctpxk.MaSP and Datepart(yyyy,pxk.NgayXuat)=DATEPART(YYYY,GETDATE()) ";
		str += " and Datepart(mm,pxk.NgayXuat)=DATEPART(MM,GETDATE()) AND Datepart(DD,pxk.NgayXuat)=DATEPART(DD,GETDATE())";
		String sql = str;

		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				SanPhamBanTrongNgay tmp = new SanPhamBanTrongNgay();
				tmp.setTenSP(rs.getString("TenSP"));
				tmp.setSl(rs.getInt("SoLuong"));
				ds.add(tmp);
			}
//		} catch (Exception ex) {
//			System.out.println(ex.getMessage());
		} catch (Exception ex) {
			Logger.getLogger(CTPXuatKhoDao.class.getName()).log(Level.SEVERE, null, ex);
			//	System.out.println(ex.getMessage());

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static int TongSoLuongDaXuatTrongNgay(int manv) {
		int tong = 0;
		String str = "Select SUM(ctpxk.SoLuong) as Tong FROM SANPHAM sp, PXUATKHO pxk, CTPXUATKHO ctpxk ";
		str += "where pxk.MaNVPhuTrach = '" + manv + "' and ctpxk.MaPXK=pxk.MaPXK ";
		str += " and sp.MaSP=ctpxk.MaSP and Datepart(yyyy,pxk.NgayXuat)=DATEPART(YYYY,GETDATE())";
		str += " and Datepart(mm,pxk.NgayXuat)=DATEPART(MM,GETDATE()) AND Datepart(DD,pxk.NgayXuat)=DATEPART(DD,GETDATE())";
		String sql = str;

		KetNoi cn = null;
		ResultSet rs = null;


		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				tong = rs.getInt("Tong");
			}
		} catch (SQLException ex) {
			Logger.getLogger(NhanVienDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		return tong;

	}

	public static double TongSanPhamDaXuat(int manv) {
		int tong = 0;
		String str = "Select Count(Distinct (ctpxk.MaSP)) as TongSanPham FROM SANPHAM sp, PXUATKHO pxk, CTPXUATKHO ctpxk ";
		str += "where pxk.MaNVPhuTrach = '" + manv + "' and ctpxk.MaPXK=pxk.MaPXK ";
		str += " and sp.MaSP=ctpxk.MaSP and Datepart(yyyy,pxk.NgayXuat)=DATEPART(YYYY,GETDATE())";
		str += " and Datepart(mm,pxk.NgayXuat)=DATEPART(MM,GETDATE()) AND Datepart(DD,pxk.NgayXuat)=DATEPART(DD,GETDATE())";
//            str += " Group by ctpxk.MaSP ";
//            str += " Having Count(ctpxk.MaSP)>1 ";

		String sql = str;
		KetNoi cn = null;
		ResultSet rs = null;

		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				tong = rs.getInt("TongSanPham");
			}
		} catch (SQLException ex) {
			Logger.getLogger(NhanVienDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		return tong;

	}

	public static int TongSoLanXuatKhoTrongNgay(int manv) {
		int tong = 0;
		String str = "Select Count(ctpxk.MaPXK) as Tong FROM SANPHAM sp, PXUATKHO pxk, CTPXUATKHO ctpxk ";
		str += "where pxk.MaNVPhuTrach = '" + manv + "' and ctpxk.MaPXK=pxk.MaPXK ";
		str += " and sp.MaSP=ctpxk.MaSP and Datepart(yyyy,pxk.NgayXuat)=DATEPART(YYYY,GETDATE())";
		str += " and Datepart(mm,pxk.NgayXuat)=DATEPART(MM,GETDATE()) AND Datepart(DD,pxk.NgayXuat)=DATEPART(DD,GETDATE())";
		String sql = str;

		KetNoi cn = null;
		ResultSet rs = null;

		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				tong = rs.getInt("Tong");
			}
		} catch (SQLException ex) {
			Logger.getLogger(NhanVienDao.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			cn.dongKetNoi();
		}
		return tong;

	}
}
